﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BoAddonBiz_FreightForwarder.Custom.Models;
using Excel = Microsoft.Office.Interop.Excel;

namespace BoAddonBiz_FreightForwarder.Custom
{
    public class GetExcelData
    {
        internal Customs GetData(string FileName)
        {
            Excel.Application myExcel = null;
            Excel.Workbook myBook = null;
            Excel.Worksheet mySheet = null;
            Excel.Range myRange = null;
            try
            {
                Customs customs = new Customs();
                myExcel = new Excel.Application();
                myExcel.Visible = false;
                myBook = myExcel.Workbooks.Open(FileName, true);
                mySheet = (Excel.Worksheet)myBook.Worksheets.Item[1];
                if (mySheet.UsedRange.Rows.Count <= 1)
                {
                    throw new Exception("Excel没有可以导入的数据，请检查EXCEL。");
                }
                string TempText = string.Empty;
                string ErrorText = string.Empty;
                //预录编号
                myRange = mySheet.UsedRange.Cells[2, 3] as Excel.Range;
                customs.UniqueId = myRange.Text.ToString().Trim();
                //申报地海关
                myRange = mySheet.UsedRange.Cells[2, 10] as Excel.Range;
                customs.ApplyCustoms = myRange.Text.ToString().Trim();
                //报关单类型
                myRange = mySheet.UsedRange.Cells[2, 6] as Excel.Range;
                customs.CustomsStyleType = myRange.Text.ToString().Trim();
                //海关编号
                myRange = mySheet.UsedRange.Cells[2, 8] as Excel.Range;
                customs.CustomsNo = myRange.Text.ToString().Trim();
                //进出口岸
                myRange = mySheet.UsedRange.Cells[3, 2] as Excel.Range;
                customs.Port = myRange.Text.ToString().Trim();
                //备案号
                myRange = mySheet.UsedRange.Cells[3, 5] as Excel.Range;
                customs.RecordNo = myRange.Text.ToString().Trim();
                //出口日期
                myRange = mySheet.UsedRange.Cells[3, 8] as Excel.Range;
                customs.Datetime = myRange.Text;
                //申报日期
                myRange = mySheet.UsedRange.Cells[3, 10] as Excel.Range;
                customs.DeclaredDate = myRange.Text;
                //经营单位
                myRange = mySheet.UsedRange.Cells[4, 2] as Excel.Range;
                TempText = myRange.Text.ToString().Trim();
                if (TempText.Contains("-") == false)
                {
                    ErrorText = "经营单位 单元格中输入的数据格式不正确，请检查。\r\n正确的格式：企业10位码-企业全称\r\n如果没有10们码可以使用NO代替";
                    SAPBoAddon.B1Addon.B1Addon.SBOApp.MessageBox(ErrorText);
                    throw new Exception(ErrorText);
                }
                customs.Company = TempText.Split('-')[0].Trim();
                customs.CompanyName = TempText.Split('-')[1].Trim();
                //运输方式
                myRange = mySheet.UsedRange.Cells[4, 4] as Excel.Range;
                TempText = myRange.Text.ToString().Trim();
                if (TempText.Contains("/") == false)
                {
                    ErrorText = "运输方式 单元格中输入的数据格式不正确，请检查。\r\n正确的格式：运输方式/航空运输";
                    SAPBoAddon.B1Addon.B1Addon.SBOApp.MessageBox(ErrorText);
                    throw new Exception(ErrorText);
                }
                customs.Transport = TempText.Split('/')[1].ToString().Trim();
                //if (customs.Transport != "航空运输")
                //{
                //    ErrorText = "运输方式 单元格中输入的数据内容不正确，请检查。\r\n正确的格式：运输方式/航空运输";
                //    SAPBoAddon.B1Addon.B1Addon.SBOApp.MessageBox(ErrorText);
                //    throw new Exception(ErrorText);
                //}
                //运输工具名称
                myRange = mySheet.UsedRange.Cells[4, 6] as Excel.Range;
                customs.Transportation = myRange.Text.ToString().Trim();
                //提运单号
                myRange = mySheet.UsedRange.Cells[4, 9] as Excel.Range;
                customs.Waybill = myRange.Text.ToString().Trim();
                //收货单位
                myRange = mySheet.UsedRange.Cells[5, 2] as Excel.Range;
                TempText = myRange.Text.ToString().Trim();
                if (TempText.Contains("-") == false)
                {
                    ErrorText = "收货单位 单元格中输入的数据格式不正确，请检查。\r\n正确的格式：企业10位码-企业全称\r\n如果没有10们码可以使用NO代替";
                    SAPBoAddon.B1Addon.B1Addon.SBOApp.MessageBox(ErrorText);
                    throw new Exception(ErrorText);
                }
                customs.DeliveryCompany = TempText.Split('-')[0].Trim();
                customs.DeliveryCompanyName = TempText.Split('-')[1].Trim();
                //贸易方式
                myRange = mySheet.UsedRange.Cells[5, 5] as Excel.Range;
                customs.TradeMothds = myRange.Text.ToString().Trim();
                //征免性质
                myRange = mySheet.UsedRange.Cells[5, 7] as Excel.Range;
                customs.Natrue = myRange.Text.ToString().Trim();
                //征税比例
                myRange = mySheet.UsedRange.Cells[5, 9] as Excel.Range;
                customs.TaxScale = myRange.Text;
                //许可证号
                myRange = mySheet.UsedRange.Cells[6, 2] as Excel.Range;
                customs.License = myRange.Text.ToString().Trim();
                //起运国
                myRange = mySheet.UsedRange.Cells[6, 4] as Excel.Range;
                TempText = myRange.Text.ToString().Trim();
                if (string.IsNullOrEmpty(TempText) == false)
                {
                    if (TempText.Split('-').Count() > 1)
                    {
                        customs.DepartureCountry = TempText.Split('-')[0];
                        customs.DepartureCountryCode = TempText.Split('-')[1];
                    }
                    else
                    {
                        customs.DepartureCountry = TempText;
                    }
                }
                //装货港
                myRange = mySheet.UsedRange.Cells[6, 6] as Excel.Range;
                TempText = myRange.Text.ToString().Trim();
                if (string.IsNullOrEmpty(TempText) == false)
                {
                    if (TempText.Split('-').Count() > 1)
                    {
                        customs.DeparturePort = TempText.Split('-')[0];
                        customs.DeparturePortCode = TempText.Split('-')[1];
                    }
                    else
                    {
                        customs.DeparturePort = TempText;
                    }
                }

                //境内目的地
                myRange = mySheet.UsedRange.Cells[6, 9] as Excel.Range;
                TempText = myRange.Text.ToString().Trim();
                if (string.IsNullOrEmpty(TempText) == false)
                {
                    if (TempText.Split('-').Count() > 1)
                    {
                        customs.Destination = TempText.Split('-')[0];
                        customs.DestinationCode = TempText.Split('-')[1];
                    }
                    else
                    {
                        customs.Destination = TempText;
                    }
                }
                //批准文号
                myRange = mySheet.UsedRange.Cells[7, 2] as Excel.Range;
                customs.JobOffer = myRange.Text.ToString().Trim();
                //成交方式
                myRange = mySheet.UsedRange.Cells[7, 4] as Excel.Range;
                customs.TradeTerms = myRange.Text.ToString().Trim();
                #region 运费
                myRange = mySheet.UsedRange.Cells[7, 6] as Excel.Range;
                TempText = myRange.Text;
                customs.FreightCurrency = TempText.Split('/')[0];
                if (TempText.Split('/').Count() > 1)
                    customs.FreightCharges = TempText.Split('/')[1];
                if (TempText.Split('/').Count() > 2)
                {
                    if (TempText.Split('/')[2].Contains("率"))
                        customs.FreightFlag = "1";
                    if (TempText.Split('/')[2].Contains("单价"))
                        customs.FreightFlag = "2";
                    if (TempText.Split('/')[2].Contains("总价"))
                        customs.FreightFlag = "3";
                }
                else
                {
                    customs.FreightFlag = "3";
                }
                #endregion
                #region 保费
                myRange = mySheet.UsedRange.Cells[7, 8] as Excel.Range;
                TempText = myRange.Text;
                customs.Premiums = TempText;
                if (customs.TradeTerms == "FOB")
                {
                    if (string.IsNullOrEmpty(customs.Premiums))
                    {
                        customs.PremiumsFlag = "1";
                        customs.Premiums = "0.3";
                    }
                    else
                    {
                        customs.PremiumsCurrency = TempText.Split('/')[0];
                        if (TempText.Split('/').Count() > 1)
                            customs.Premiums = TempText.Split('/')[1];
                        if (TempText.Split('/').Count() > 2)
                        {
                            if (TempText.Split('/')[2].Contains("率"))
                                customs.PremiumsFlag = "1";
                            if (TempText.Split('/')[2].Contains("单价"))
                                customs.PremiumsFlag = "2";
                            if (TempText.Split('/')[2].Contains("总价"))
                                customs.PremiumsFlag = "3";
                        }
                        else
                        {
                            customs.PremiumsFlag = "3";
                        }
                    }
                }
                #endregion
                #region 杂费
                myRange = mySheet.UsedRange.Cells[7, 10] as Excel.Range;
                TempText = myRange.Text;
                customs.OtherCharges = TempText;
                if (string.IsNullOrEmpty(customs.OtherCharges) == false)
                {
                    customs.OtherChargesCurrency = TempText.Split('/')[0];
                    if (TempText.Split('/').Count() > 1)
                        customs.OtherCharges = TempText.Split('/')[1];
                    if (TempText.Split('/').Count() > 2)
                    {
                        if (TempText.Split('/')[2].Contains("率"))
                            customs.OtherChargesFlag = "1";
                        if (TempText.Split('/')[2].Contains("单价"))
                            customs.OtherChargesFlag = "2";
                        if (TempText.Split('/')[2].Contains("总价"))
                            customs.OtherChargesFlag = "3";
                    }
                    else
                    {
                        customs.OtherChargesFlag = "3";
                    }
                }

                #endregion
                //合同协议号
                myRange = mySheet.UsedRange.Cells[8, 2] as Excel.Range;
                customs.Contract = myRange.Text.ToString().Trim();
                //件数
                myRange = mySheet.UsedRange.Cells[8, 4] as Excel.Range;
                customs.Quantity = myRange.Text;
                //包装种类
                myRange = mySheet.UsedRange.Cells[8, 6] as Excel.Range;
                customs.PackageType = myRange.Text.ToString().Trim();
                //毛重
                myRange = mySheet.UsedRange.Cells[8, 8] as Excel.Range;
                customs.GrossWeight = myRange.Text;
                //净重
                myRange = mySheet.UsedRange.Cells[8, 10] as Excel.Range;
                customs.SuttleWeight = myRange.Text;
                //集装箱号
                myRange = mySheet.UsedRange.Cells[9, 2] as Excel.Range;
                customs.Container = myRange.Text.ToString().Trim();
                //随附单据
                myRange = mySheet.UsedRange.Cells[9, 4] as Excel.Range;
                customs.Documents = myRange.Text;
                //用途
                myRange = mySheet.UsedRange.Cells[9, 10] as Excel.Range;
                customs.Usage = myRange.Text.ToString().Trim();
                //唛头
                myRange = mySheet.UsedRange.Cells[10, 2] as Excel.Range;
                customs.ShippingMark = myRange.Text.ToString().Trim();
                int i = 12;
                while (true)
                {
                    myRange = mySheet.UsedRange.Cells[i, 1] as Excel.Range;
                    if (myRange.Text == "税费征收情况")
                    {
                        break;
                    }
                    var line = customs.Lines.New();
                    //行号
                    myRange = mySheet.UsedRange.Cells[i, 1] as Excel.Range;
                    line.RowId = myRange.Text;
                    //商品编码
                    myRange = mySheet.UsedRange.Cells[i, 2] as Excel.Range;
                    line.ItemCode = myRange.Text;
                    line.ItemCode = line.ItemCode.Trim();
                    if (line.ItemCode.Length > 10)
                    {
                        ErrorText = "商品编码 " + line.ItemCode + " 大于10位，请检查。";
                        SAPBoAddon.B1Addon.B1Addon.SBOApp.MessageBox(ErrorText);
                        throw new Exception(ErrorText);
                    }
                    line.ItemCode += "0000000000".Substring(0, 10 - line.ItemCode.Length);
                    //商品描述
                    myRange = mySheet.UsedRange.Cells[i, 3] as Excel.Range;
                    TempText = myRange.Text.ToString().Trim();
                    line.Description = TempText.Split('/')[0];
                    line.Standard = TempText.Split('/')[1];
                    #region 数量/单位
                    myRange = mySheet.UsedRange.Cells[i, 5] as Excel.Range;
                    TempText = myRange.Text.ToString().Trim();
                    if (TempText.Contains("/") == false)
                    {
                        ErrorText = "第 " + line.RowId.ToString() + " 行数量和单位的录入格式有问题，请检查。";
                        SAPBoAddon.B1Addon.B1Addon.SBOApp.MessageBox(ErrorText);
                        throw new Exception(ErrorText);
                    }
                    if (string.IsNullOrEmpty(TempText) == false)
                    {
                        string[] QuantityUnit = TempText.Split('-');
                        string Quantity = QuantityUnit[0].Split('/')[0];
                        line.FirstQuantity = string.IsNullOrEmpty(Quantity) ? "0" : Quantity;
                        line.FirstUnit = QuantityUnit[0].Split('/')[1];
                        if (QuantityUnit.Count() > 1)
                        {
                            Quantity = QuantityUnit[1].Split('/')[0];
                            line.SecondQuantity = string.IsNullOrEmpty(Quantity) ? "0" : Quantity;
                            line.SecondUnit = QuantityUnit[1].Split('/')[1];
                        }
                        if (QuantityUnit.Count() > 2)
                        {
                            Quantity = QuantityUnit[2].Split('/')[0];
                            line.ThirdQuantity = string.IsNullOrEmpty(Quantity) ? "0" : Quantity;
                            line.ThirdUnit = QuantityUnit[2].Split('/')[1];
                        }
                        else
                        {
                            line.ThirdUnit = line.FirstUnit;
                            line.ThirdQuantity = line.FirstQuantity;
                        }
                    }
                    #endregion
                    //原产国
                    myRange = mySheet.UsedRange.Cells[i, 7] as Excel.Range;
                    line.Country = myRange.Text.ToString().Trim();
                    //总价
                    myRange = mySheet.UsedRange.Cells[i, 8] as Excel.Range;
                    line.Amount = myRange.Text;
                    //币种
                    myRange = mySheet.UsedRange.Cells[i, 9] as Excel.Range;
                    line.Currency = myRange.Text.ToString().Trim();
                    //征免
                    myRange = mySheet.UsedRange.Cells[i, 10] as Excel.Range;
                    line.Nature = myRange.Text.ToString().Trim();

                    i++;
                }
                //税费征收情况
                myRange = mySheet.UsedRange.Cells[i + 1, 2] as Excel.Range;
                customs.Tax = myRange.Text;
                //录入员
                myRange = mySheet.UsedRange.Cells[i + 2, 2] as Excel.Range;
                customs.Entry = myRange.Text.ToString().Trim();
                //报关员
                myRange = mySheet.UsedRange.Cells[i + 4, 2] as Excel.Range;
                customs.Broker = myRange.Text.ToString().Trim();
                //录入单位
                myRange = mySheet.UsedRange.Cells[i + 3, 4] as Excel.Range;
                customs.EntryCompany = myRange.Text.ToString().Trim();
                myBook.Close(false);
                myExcel.Workbooks.Close();
                myExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
                GC.Collect();
                return customs;
            }
            catch (Exception ex)
            {
                ReleseExcel(myExcel, myBook, mySheet);
                throw ex;
            }
        }
        private void ReleseExcel(Excel.Application myExcel, Excel.Workbook myBook, Excel.Worksheet mySheet)
        {
            if (myExcel != null)
            {
                myBook.Close(false);
                myExcel.Workbooks.Close();
                myExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
            }
            GC.Collect();
        }
    }
}
